﻿using System; 
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Matrix.Common.DBUtility;
using Matrix.MaKaYunShang.Model;
using Matrix.MaKaYunShang.Model.Dto;

namespace Matrix.MaKaYunShang.DAL
{
	public partial class CloudCardsDAL : BaseDAL<CloudCardsDAL>
	{
        #region 默认方法
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(CloudCards model)
        {
            var strSql = new StringBuilder();
            strSql.Append("insert into CloudCards(");
            strSql.Append("CardName,CardCategoryId,CategoryCode,ShopId,CardKey,TotalQuantity,SendQuantity,ReceiveQuantity,Description,LoGo,Color,Brand,Subtitle,Title,StartTime,EndTime,DescriptionImage,LeastCost,ReduceCost,DiscountValue,CardValue,CanShare,Instruction,Disclaimer,GoldValue,IsCanTrade,FaidReason,PassTime,PassUserId,AddUserId,PassStatus,UsableShopIds,ShowOnHomepage,DisabledSeparatedIndustryIds,AddTime,IsDelete");
            strSql.Append(") values (");
            strSql.Append("@CardName,@CardCategoryId,@CategoryCode,@ShopId,@CardKey,@TotalQuantity,@SendQuantity,@ReceiveQuantity,@Description,@LoGo,@Color,@Brand,@Subtitle,@Title,@StartTime,@EndTime,@DescriptionImage,@LeastCost,@ReduceCost,@DiscountValue,@CardValue,@CanShare,@Instruction,@Disclaimer,@GoldValue,@IsCanTrade,@FaidReason,@PassTime,@PassUserId,@AddUserId,@PassStatus,@UsableShopIds,@ShowOnHomepage,@DisabledSeparatedIndustryIds,@AddTime,@IsDelete");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
			            new SqlParameter("@CardName", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@CardCategoryId", SqlDbType.Int,4) ,            
                        new SqlParameter("@CategoryCode", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@ShopId", SqlDbType.Int,4) ,            
                        new SqlParameter("@CardKey", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@TotalQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@SendQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@ReceiveQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@Description", SqlDbType.NVarChar,2048) ,            
                        new SqlParameter("@LoGo", SqlDbType.NVarChar,256) ,            
                        new SqlParameter("@Color", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@Brand", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@Subtitle", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@Title", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@StartTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@EndTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@DescriptionImage", SqlDbType.NVarChar,256) ,            
                        new SqlParameter("@LeastCost", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@ReduceCost", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@DiscountValue", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@CardValue", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@CanShare", SqlDbType.Bit,1) ,            
                        new SqlParameter("@Instruction", SqlDbType.NVarChar,256) ,            
                        new SqlParameter("@Disclaimer", SqlDbType.NVarChar,256) ,            
                        new SqlParameter("@GoldValue", SqlDbType.Int,4) ,            
                        new SqlParameter("@IsCanTrade", SqlDbType.Bit,1) ,            
                        new SqlParameter("@FaidReason", SqlDbType.NVarChar,256) ,            
                        new SqlParameter("@PassTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@PassUserId", SqlDbType.Int,4) ,            
                        new SqlParameter("@AddUserId", SqlDbType.Int,4) ,            
                        new SqlParameter("@PassStatus", SqlDbType.Int,4) ,            
                        new SqlParameter("@UsableShopIds", SqlDbType.NVarChar,2048) ,            
                        new SqlParameter("@ShowOnHomepage", SqlDbType.Bit,1) ,            
                        new SqlParameter("@DisabledSeparatedIndustryIds", SqlDbType.NVarChar,2048) ,            
                        new SqlParameter("@AddTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.CardName;
            parameters[1].Value = model.CardCategoryId;
            parameters[2].Value = model.CategoryCode;
            parameters[3].Value = model.ShopId;
            parameters[4].Value = model.CardKey;
            parameters[5].Value = model.TotalQuantity;
            parameters[6].Value = model.SendQuantity;
            parameters[7].Value = model.ReceiveQuantity;
            parameters[8].Value = model.Description;
            parameters[9].Value = model.LoGo;
            parameters[10].Value = model.Color;
            parameters[11].Value = model.Brand;
            parameters[12].Value = model.Subtitle;
            parameters[13].Value = model.Title;
            parameters[14].Value = model.StartTime;
            parameters[15].Value = model.EndTime;
            parameters[16].Value = model.DescriptionImage;
            parameters[17].Value = model.LeastCost;
            parameters[18].Value = model.ReduceCost;
            parameters[19].Value = model.DiscountValue;
            parameters[20].Value = model.CardValue;
            parameters[21].Value = model.CanShare;
            parameters[22].Value = model.Instruction;
            parameters[23].Value = model.Disclaimer;
            parameters[24].Value = model.GoldValue;
            parameters[25].Value = model.IsCanTrade;
            parameters[26].Value = model.FaidReason;
            parameters[27].Value = model.PassTime;
            parameters[28].Value = model.PassUserId;
            parameters[29].Value = model.AddUserId;
            parameters[30].Value = model.PassStatus;
            parameters[31].Value = model.UsableShopIds;
            parameters[32].Value = model.ShowOnHomepage;
            parameters[33].Value = model.DisabledSeparatedIndustryIds;
            parameters[34].Value = model.AddTime;
            parameters[35].Value = model.IsDelete; var obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(CloudCards model)
        {
            var strSql = new StringBuilder();
            strSql.Append("update CloudCards set ");

            strSql.Append(" CardName = @CardName , ");
            strSql.Append(" CardCategoryId = @CardCategoryId , ");
            strSql.Append(" CategoryCode = @CategoryCode , ");
            strSql.Append(" ShopId = @ShopId , ");
            strSql.Append(" CardKey = @CardKey , ");
            strSql.Append(" TotalQuantity = @TotalQuantity , ");
            strSql.Append(" SendQuantity = @SendQuantity , ");
            strSql.Append(" ReceiveQuantity = @ReceiveQuantity , ");
            strSql.Append(" Description = @Description , ");
            strSql.Append(" LoGo = @LoGo , ");
            strSql.Append(" Color = @Color , ");
            strSql.Append(" Brand = @Brand , ");
            strSql.Append(" Subtitle = @Subtitle , ");
            strSql.Append(" Title = @Title , ");
            strSql.Append(" StartTime = @StartTime , ");
            strSql.Append(" EndTime = @EndTime , ");
            strSql.Append(" DescriptionImage = @DescriptionImage , ");
            strSql.Append(" LeastCost = @LeastCost , ");
            strSql.Append(" ReduceCost = @ReduceCost , ");
            strSql.Append(" DiscountValue = @DiscountValue , ");
            strSql.Append(" CardValue = @CardValue , ");
            strSql.Append(" CanShare = @CanShare , ");
            strSql.Append(" Instruction = @Instruction , ");
            strSql.Append(" Disclaimer = @Disclaimer , ");
            strSql.Append(" GoldValue = @GoldValue , ");
            strSql.Append(" IsCanTrade = @IsCanTrade , ");
            strSql.Append(" FaidReason = @FaidReason , ");
            strSql.Append(" PassTime = @PassTime , ");
            strSql.Append(" PassUserId = @PassUserId , ");
            strSql.Append(" AddUserId = @AddUserId , ");
            strSql.Append(" PassStatus = @PassStatus , ");
            strSql.Append(" UsableShopIds = @UsableShopIds , ");
            strSql.Append(" ShowOnHomepage = @ShowOnHomepage , ");
            strSql.Append(" DisabledSeparatedIndustryIds = @DisabledSeparatedIndustryIds , ");
            strSql.Append(" AddTime = @AddTime , ");
            strSql.Append(" IsDelete = @IsDelete  ");
            strSql.Append(" where Id=@Id ");

            SqlParameter[] parameters = {
			            new SqlParameter("@Id", SqlDbType.Int,4) ,            
                        new SqlParameter("@CardName", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@CardCategoryId", SqlDbType.Int,4) ,            
                        new SqlParameter("@CategoryCode", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@ShopId", SqlDbType.Int,4) ,            
                        new SqlParameter("@CardKey", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@TotalQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@SendQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@ReceiveQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@Description", SqlDbType.NVarChar,2048) ,            
                        new SqlParameter("@LoGo", SqlDbType.NVarChar,256) ,            
                        new SqlParameter("@Color", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@Brand", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@Subtitle", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@Title", SqlDbType.NVarChar,32) ,            
                        new SqlParameter("@StartTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@EndTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@DescriptionImage", SqlDbType.NVarChar,256) ,            
                        new SqlParameter("@LeastCost", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@ReduceCost", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@DiscountValue", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@CardValue", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@CanShare", SqlDbType.Bit,1) ,            
                        new SqlParameter("@Instruction", SqlDbType.NVarChar,256) ,            
                        new SqlParameter("@Disclaimer", SqlDbType.NVarChar,256) ,            
                        new SqlParameter("@GoldValue", SqlDbType.Int,4) ,            
                        new SqlParameter("@IsCanTrade", SqlDbType.Bit,1) ,            
                        new SqlParameter("@FaidReason", SqlDbType.NVarChar,256) ,            
                        new SqlParameter("@PassTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@PassUserId", SqlDbType.Int,4) ,            
                        new SqlParameter("@AddUserId", SqlDbType.Int,4) ,            
                        new SqlParameter("@PassStatus", SqlDbType.Int,4) ,            
                        new SqlParameter("@UsableShopIds", SqlDbType.NVarChar,2048) ,            
                        new SqlParameter("@ShowOnHomepage", SqlDbType.Bit,1) ,            
                        new SqlParameter("@DisabledSeparatedIndustryIds", SqlDbType.NVarChar,2048) ,            
                        new SqlParameter("@AddTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.Id;
            parameters[1].Value = model.CardName;
            parameters[2].Value = model.CardCategoryId;
            parameters[3].Value = model.CategoryCode;
            parameters[4].Value = model.ShopId;
            parameters[5].Value = model.CardKey;
            parameters[6].Value = model.TotalQuantity;
            parameters[7].Value = model.SendQuantity;
            parameters[8].Value = model.ReceiveQuantity;
            parameters[9].Value = model.Description;
            parameters[10].Value = model.LoGo;
            parameters[11].Value = model.Color;
            parameters[12].Value = model.Brand;
            parameters[13].Value = model.Subtitle;
            parameters[14].Value = model.Title;
            parameters[15].Value = model.StartTime;
            parameters[16].Value = model.EndTime;
            parameters[17].Value = model.DescriptionImage;
            parameters[18].Value = model.LeastCost;
            parameters[19].Value = model.ReduceCost;
            parameters[20].Value = model.DiscountValue;
            parameters[21].Value = model.CardValue;
            parameters[22].Value = model.CanShare;
            parameters[23].Value = model.Instruction;
            parameters[24].Value = model.Disclaimer;
            parameters[25].Value = model.GoldValue;
            parameters[26].Value = model.IsCanTrade;
            parameters[27].Value = model.FaidReason;
            parameters[28].Value = model.PassTime;
            parameters[29].Value = model.PassUserId;
            parameters[30].Value = model.AddUserId;
            parameters[31].Value = model.PassStatus;
            parameters[32].Value = model.UsableShopIds;
            parameters[33].Value = model.ShowOnHomepage;
            parameters[34].Value = model.DisabledSeparatedIndustryIds;
            parameters[35].Value = model.AddTime;
            parameters[36].Value = model.IsDelete; return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("update CloudCards set IsDelete=1 ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        public bool DeleteList(string Idlist)
        {
            var strSql = new StringBuilder();
            strSql.Append("update CloudCards set IsDelete=1 ");
            strSql.Append(" where Id in (" + Idlist + ")  ");
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataSet GetModel(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("select Id, CardName, CardCategoryId, CategoryCode, ShopId, CardKey, TotalQuantity, SendQuantity, ReceiveQuantity, Description, LoGo, Color, Brand, Subtitle, Title, StartTime, EndTime, DescriptionImage, LeastCost, ReduceCost, DiscountValue, CardValue, CanShare, Instruction, Disclaimer, GoldValue, IsCanTrade, FaidReason, PassTime, PassUserId, AddUserId, PassStatus, UsableShopIds, ShowOnHomepage, DisabledSeparatedIndustryIds, AddTime, IsDelete  ");
            strSql.Append("  from CloudCards ");
            strSql.Append(" where Id=@Id  and IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            var ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM CloudCards where IsDelete=0 ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion

        #region 扩展方法

        public DataSet GetModelById(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("select s.Name as ShopName, CloudCards.Id, CardName, CardCategoryId, CategoryCode, ShopId, CardKey, TotalQuantity, SendQuantity, ReceiveQuantity, CloudCards.Description, CloudCards.LoGo, Color, Brand, Subtitle, Title, StartTime, EndTime, DescriptionImage, LeastCost, ReduceCost, DiscountValue, CardValue, CanShare, Instruction, Disclaimer, GoldValue, IsCanTrade, FaidReason, PassTime, PassUserId, AddUserId, PassStatus, UsableShopIds, ShowOnHomepage, DisabledSeparatedIndustryIds, AddTime, CloudCards.IsDelete    ");
            strSql.Append("  from CloudCards left join shops s on s.id= CloudCards.ShopId ");
            strSql.Append(" where CloudCards.Id=@Id  and CloudCards.IsDelete=0 and s.IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            var ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }

        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <returns></returns>
        public List<ViewCloudCardsDto> GetPagingList(int pageIndex, int pageSize, string keyWord, int cateId, int status, string shopids, string colOrder, ref int totalCount)
        {
            var strWhere = new StringBuilder(" IsDelete=0 ");
            var parameters = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(keyWord))
            {
                strWhere.Append(" and [CardName] like @keyWord");
                parameters.Add(new SqlParameter { ParameterName = "@keyWord", Value = "%" + keyWord + "%" });
            }
            if (cateId > 0)
            {
                strWhere.Append(" and CardCategoryId=" + cateId);
            }
            if (status >= 0)
            {
                strWhere.Append(" and PassStatus=" + status);
            }
            if (!string.IsNullOrEmpty(shopids))
            {
                strWhere.Append(" and ShopId in (" + shopids.Trim(',') + ")");
            }
            return GetSimpleListPage<ViewCloudCardsDto>(pageIndex, pageSize, "CloudCards t1", null, "Id, CardName, (select CategoryName from CardCategory t2 where t2.Id=t1.CardCategoryId) as CategoryName, LoGo, PassStatus, ShowOnHomepage, AddTime, (select Name from Shops t3 where t3.Id=t1.ShopId) as ShopName", colOrder, strWhere.ToString(), ref totalCount, parameters.ToArray());
        }
        #endregion
	}
}
